package com.young.cms.service.impl;

import com.young.cms.model.Column;
import com.young.cms.model.Table;
import com.young.cms.service.ICmsDBService;
import com.young.cms.util.CmsConstants;
import com.young.common.exception.BaseException;
import com.young.common.util.StringUtils;
import com.young.common.core.dal.EDBType;
import com.young.common.core.dal.service.IDataAccessService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * 数据库操作服务
 * Created by rookie on 2017/9/12.
 */
@Service("cmsDBService")
public class CmsDBServiceImpl implements ICmsDBService {

    @Resource(name = "dataAccessService")
    IDataAccessService dataAccessService;//数据层服务

    private static final Logger logger = LoggerFactory.getLogger(CmsDBServiceImpl.class);

    /**
     * 生成建表时的字段部分sql,例如 NAME VARCHAR2(32) NOT NULL
     * 根据mysql或Oracle生成对应语句
     * @param col
     * @return
     */
    private StringBuilder getColumnSql(Column col) {
        StringBuilder sb = new StringBuilder("");
        if (EDBType.MYSQL == dataAccessService.getDBType()){//mysql数据库
            if (CmsConstants.ColumnType.CHAR.equalsIgnoreCase(col.getType()) ){
                //字符类型
                sb.append(col.getField()).append(" ").append("CHAR").append("(").append(col.getLength()).append(")");
            }else if (CmsConstants.ColumnType.VARCHAR2.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.VARCHAR.equalsIgnoreCase(col.getType()) ){
                //字符串类型
                sb.append(col.getField()).append(" ").append("VARCHAR").append("(").append(col.getLength()).append(")");
            }else if (CmsConstants.ColumnType.NUMBER.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.INT.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.INTEGER.equalsIgnoreCase(col.getType()) ){
                //数字类型,长度限制在37以内
                if (col.getLength() > 38){
                    throw new BaseException("数字类型字段超出长度限制,最大允许长度为37!");
                }
                sb.append(col.getField()).append(" ").append("INT").append("(").append(col.getLength()).append(")");
            }/*else if (CmsConstants.ColumnType.DATE.equals(col.getType()) || CmsConstants.ColumnType.CLOB.equals(col.getType()) ){
                //时间类型和大文本类型,不设置长度
                sb.append(col.getField()).append(" ").append("TEXT");
            }*/else if (CmsConstants.ColumnType.DATE.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.DATETIME.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.TIMESTAMP.equalsIgnoreCase(col.getType()) ){
                //时间类型不设置长度
                sb.append(col.getField()).append(" ").append("DATETIME");
            }else if (CmsConstants.ColumnType.CLOB.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.TEXT.equalsIgnoreCase(col.getType()) ){
                //大文本类型不设置长度
                sb.append(col.getField()).append(" ").append("TEXT");
            }else if (CmsConstants.ColumnType.FLOAT.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.DOUBLE.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.NUMERIC.equalsIgnoreCase(col.getType()) ){
                //浮点类型
                sb.append(col.getField()).append(" ").append("DOUBLE").append("(").append(col.getLength()).append(",").append(col.getFloatLength()).append(")");
            }else{
                throw new BaseException("无法识别的字段类型:"+col.getType());
            }
        } else if (EDBType.ORACLE == dataAccessService.getDBType()){//oracle数据库
            if (CmsConstants.ColumnType.CHAR.equalsIgnoreCase(col.getType()) ){
                //字符类型
                sb.append(col.getField()).append(" ").append("CHAR").append("(").append(col.getLength()).append(")");
            }else if (CmsConstants.ColumnType.VARCHAR2.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.VARCHAR.equalsIgnoreCase(col.getType()) ){
                //字符串类型
                sb.append(col.getField()).append(" ").append("VARCHAR2").append("(").append(col.getLength()).append(")");
            }else if (CmsConstants.ColumnType.NUMBER.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.INT.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.INTEGER.equalsIgnoreCase(col.getType())){
                //数字类型,长度限制在37以内
                if (col.getLength() > 38){
                    throw new BaseException("数字类型字段超出长度限制,最大允许长度为37!");
                }
                sb.append(col.getField()).append(" ").append("NUMBER").append("(").append(col.getLength()).append(")");
            }/*else if (CmsConstants.ColumnType.DATE.equals(col.getType()) || CmsConstants.ColumnType.CLOB.equals(col.getType()) ){
                //时间类型和大文本类型,不设置长度
                sb.append(col.getField()).append(" ").append("CLOB");
            }*/else if (CmsConstants.ColumnType.DATE.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.DATETIME.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.TIMESTAMP.equalsIgnoreCase(col.getType())){
                //时间类型不设置长度
                sb.append(col.getField()).append(" ").append("DATE");
            }else if (CmsConstants.ColumnType.CLOB.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.TEXT.equalsIgnoreCase(col.getType()) ){
                //大文本类型不设置长度
                sb.append(col.getField()).append(" ").append("CLOB");
            }else if (CmsConstants.ColumnType.FLOAT.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.DOUBLE.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.NUMERIC.equalsIgnoreCase(col.getType()) ){
                //浮点类型
                sb.append(col.getField()).append(" ").append("NUMBER").append("(").append(col.getLength()).append(",").append(col.getFloatLength()).append(")");
            }else{
                throw new BaseException("无法识别的字段类型:"+col.getType());
            }
        } else if (EDBType.POSTGRESQL == dataAccessService.getDBType()){//postgre数据库
            if (CmsConstants.ColumnType.CHAR.equalsIgnoreCase(col.getType()) ){
                //字符类型
                sb.append(col.getField()).append(" ").append("CHAR").append("(").append(col.getLength()).append(")");
            }else if (CmsConstants.ColumnType.VARCHAR2.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.VARCHAR.equalsIgnoreCase(col.getType()) ){
                //字符串类型
                sb.append(col.getField()).append(" ").append("VARCHAR").append("(").append(col.getLength()).append(")");
            }else if (CmsConstants.ColumnType.NUMBER.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.INT.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.INTEGER.equalsIgnoreCase(col.getType())){
                //postgres的数字类型不需要设置长度
                sb.append(col.getField()).append(" ").append("integer");
            }else if (CmsConstants.ColumnType.DATE.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.DATETIME.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.TIMESTAMP.equalsIgnoreCase(col.getType())){
                //时间类型不设置长度
                sb.append(col.getField()).append(" ").append("TIMESTAMP");
            }else if (CmsConstants.ColumnType.CLOB.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.TEXT.equalsIgnoreCase(col.getType()) ){
                //大文本类型不设置长度
                sb.append(col.getField()).append(" ").append("TEXT");
            }else if (CmsConstants.ColumnType.FLOAT.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.DOUBLE.equalsIgnoreCase(col.getType()) || CmsConstants.ColumnType.NUMERIC.equalsIgnoreCase(col.getType()) ){
                //浮点类型
                sb.append(col.getField()).append(" ").append("NUMERIC").append("(").append(col.getLength()).append(",").append(col.getFloatLength()).append(")");
            }else{
                throw new BaseException("无法识别的字段类型:"+col.getType());
            }
        }else{
            throw new BaseException("不支持的数据库类型,目前只支持MySQL/Oracle/Postgres三种类型的数据库!");
        }
        if ("1".equals(col.getNotNull())){
            sb.append(" NOT NULL");
        }
        return sb;
    }

    /**
     * 处理字段的实际在数据库中的类型
     * @param column
     */
    private void handleColumnProType(Column column) {
        if (EDBType.MYSQL == dataAccessService.getDBType()){
            if (CmsConstants.ColumnType.CHAR.equals(column.getType()) ){
                //字符类型
                column.setProType("CHAR");
            }else if (CmsConstants.ColumnType.VARCHAR2.equals(column.getType()) ){
                //字符串类型
                column.setProType("VARCHAR");
            }else if (CmsConstants.ColumnType.NUMBER.equals(column.getType())){
                //数字类型
                column.setProType("INT");
            }else if (CmsConstants.ColumnType.DATE.equals(column.getType())){
                //时间类型
                column.setProType("datetime");
            }else if (CmsConstants.ColumnType.CLOB.equals(column.getType())){
                //大文本类型
                column.setProType("TEXT");
            }else if (CmsConstants.ColumnType.FLOAT.equals(column.getType()) ){
                //浮点类型
                column.setProType("DOUBLE");
            }
        }else if (EDBType.ORACLE == dataAccessService.getDBType()){
            if (CmsConstants.ColumnType.CHAR.equals(column.getType()) ){
                //字符类型
                column.setProType("CHAR");
            }else if (CmsConstants.ColumnType.VARCHAR2.equals(column.getType()) ){
                //字符串类型
                column.setProType("VARCHAR2");
            }else if (CmsConstants.ColumnType.NUMBER.equals(column.getType())){
                //数字类型
                column.setProType("NUMBER");
            }else if (CmsConstants.ColumnType.DATE.equals(column.getType())){
                //时间类型
                column.setProType("DATE");
            }else if (CmsConstants.ColumnType.CLOB.equals(column.getType())){
                //大文本类型
                column.setProType("CLOB");
            }else if (CmsConstants.ColumnType.FLOAT.equals(column.getType()) ){
                //浮点类型
                column.setProType("NUMBER");
            }
        }
    }

    @Override
    public boolean existTable(String tableId) {
        int c = (Integer)dataAccessService.getObject(CmsConstants.MAPPER.CMS_DB + ".countTableById", tableId);
        if (c > 0){
            return true;
        }else{
            return false;
        }
    }

    @Override
    public boolean createTable(Table table) {
        if (table != null && table.getColumnList() != null && table.getColumnList().size() > 0){
            if (existTable(table.getId())){
                throw new BaseException("创建表失败:已存在表"+table.getId());
            }
            StringBuilder sb = new StringBuilder("CREATE TABLE ");
            sb.append(table.getId()).append(" (");
            List<Column> list = table.getColumnList();
            for(int i=0;i<list.size();i++){
                Column col = list.get(i);//字段
                if (i != 0){
                    sb.append(",");
                }
                sb.append(getColumnSql(col));
            }
            sb.append(")");
            logger.info("[创建表] SQL={}", sb);
            dataAccessService.update(CmsConstants.MAPPER.CMS_DB + ".createTable", sb.toString());
        }else{
            logger.info("[创建表] 失败:表或者字段无效!{}", table);
        }
        return false;
    }

    @Override
    public boolean dropTable(Table table) {
        if (table != null && StringUtils.isNotBlank(table.getId())){
            logger.info("[删除表] 表名:{}", table.getId());
            dataAccessService.update(CmsConstants.MAPPER.CMS_DB + ".dropTable", table.getId());
            return true;
        }else{
            logger.info("[删除表] 失败:表参数无效!{}", table);
            return false;
        }
    }

    @Override
    public boolean dropTable(String tableId) {
        logger.info("[删除表] 表名:{}", tableId);
        dataAccessService.update(CmsConstants.MAPPER.CMS_DB + ".dropTable", tableId);
        return true;
    }

    @Override
    public boolean addColumn(Column column) {
        logger.info("[添加字段] 字段信息:{}", column);
        handleColumnProType(column);
        dataAccessService.update(CmsConstants.MAPPER.CMS_DB + ".addColumn", column);
        return true;
    }

    @Override
    public boolean modifyColumn(Column column) {
        logger.info("[修改字段] 字段信息:{}", column);
        handleColumnProType(column);
        dataAccessService.update(CmsConstants.MAPPER.CMS_DB + ".modifyColumn", column);
        return true;
    }

    @Override
    public boolean deleteColumn(Column column) {
        logger.info("[删除字段] 字段信息:{}", column);
        dataAccessService.update(CmsConstants.MAPPER.CMS_DB + ".deleteColumn", column);
        return true;
    }

    @Override
    public List<Table> getTableListOfSimpleInfo() {
        return  dataAccessService.getList(CmsConstants.MAPPER.CMS_DB + ".getTableListOfSimpleInfo", null);
    }

    @Override
    public List<Column> getColumsByTableName(String tableName) {
        return dataAccessService.getList(CmsConstants.MAPPER.CMS_DB + ".getColumsListByTableName", tableName);
    }
}
